package moveMysql;

import java.io.File;
import java.io.FileInputStream;
import java.io.ObjectInputStream;
import java.sql.*;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static configure.InputConfig.*;
import static moveMysql.Constant.*;

public class InputMysql {

    static Connection conn = null;
    static Statement stmt = null;

    public static void main(String[] args) {
        mysqlDataIn();
    }

    public static void mysqlDataIn() {
        String[] createTableSqls = {SQL_CREATE_SUBSITE_TABLE, SQL_CREATE_SUBJECT_TABLE, SQL_CREATE_CASE_ENTITY_TABLE, SQL_CREATE_CASE_EXTENDS_TABLE, SQL_CREATE_Target_TABLE, SQL_CREATE_Target_Extends_TABLE, SQL_CREATE_CASE_PERMISSION_TABLE, SQL_CREATE_TARGET_PERMISSION_TABLE};
        //判断数据库是否存在，不存在则新建
        testDB();
        //判断case_entity表是否存在，不存在则新建
        testTable(tableNames, createTableSqls);
        //读取文件
        File file = new File(MYSQL_FILEPATH + MYSQL_FILENAME);
        HashMap<String, Object> maps = (HashMap<String, Object>) (readObjectFromDocument(file));
        //从对象中解析出数据
        LinkedList<Subsite> subsites = (LinkedList<Subsite>) (maps.get("subsites"));
        LinkedList<Subject> subjects = (LinkedList<Subject>) (maps.get("subjects"));
        LinkedList<CaseEntity> caseEntities = (LinkedList<CaseEntity>) (maps.get("caseEntities"));
        LinkedList<CaseExtends> caseExtends = (LinkedList<CaseExtends>) (maps.get("caseExtends"));
        LinkedList<Target> targets = (LinkedList<Target>) (maps.get("targets"));
        LinkedList<TargetExtends> targetExtends = (LinkedList<TargetExtends>) (maps.get("targetExtends"));
        //插入数据
        insertIntoMySQL(PROPERTIES_PATH, OWNER_ID, subsites, subjects, caseEntities, caseExtends, targets, targetExtends);
    }

    /**
     * @param
     * @return boolean
     * @Description 向数据库中插入数据
     * @author 孙加辉
     * @date 2018/10/31 10:20
     */
    private static boolean insertIntoMySQL(String propertiesPath,
                                           String ownerId,
                                           LinkedList<Subsite> subsites,
                                           LinkedList<Subject> subjects,
                                           LinkedList<CaseEntity> linkedList,
                                           LinkedList<CaseExtends> caseExtendsList,
                                           LinkedList<Target> targetList,
                                           LinkedList<TargetExtends> targetExtendsList) {
        boolean isInsertSuccess = true;
        //从文档中读取对象
        String caseEntitySql = "replace into case_entity values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        String caseExtendsSql = "replace into case_extends values(?,?,?,?,?)";
        String targetSql = "replace into target values(?,?,?,?,?,?,?,?,?)";
        String targetExtendsSql = "replace into target_extends values(?,?,?,?)";
        String casePermissionSql = "replace into case_permission(user_id, operation, instance_id, create_time) values(?,?,?,?)";
        String targerPermissionSql = "replace into target_permission(user_id, operation, instance_id, create_time) values(?,?,?,?)";
        String subjectSql = "replace into subject values(?,?,?,?,?)";
        String subsiteSql = "replace into subsite values(?,?,?,?,?,?)";
        String closeForeignKeyCheckSql = "SET FOREIGN_KEY_CHECKS = 0;";
        String openForeignCheckSql = "SET FOREIGN_KEY_CHECKS = 1;";
        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            //关闭外键检测
            stmt.execute(closeForeignKeyCheckSql);
            //插入subsite表
            if (null != subsites) {
                System.out.println("正在写subsite表");
                for (Subsite e : subsites) {
                    PreparedStatement pstmt = (PreparedStatement) (conn.prepareStatement(subsiteSql));
                    pstmt.setLong(1, e.getId());
                    pstmt.setString(2, e.getName());
                    pstmt.setString(3, e.getBaseUrl());
                    pstmt.setString(4, e.getType());
                    pstmt.setString(5, e.getWorkerEntranceUrl());
                    pstmt.setString(6, e.getManagerEntranceUrl());
                    pstmt.executeUpdate();

                }
                System.out.println("subsite表导入完成");
            } else {
                isInsertSuccess = false;
            }
            //插入subject表
            if (null != subjects) {
                System.out.println("正在写subject表");


                for (Subject e : subjects) {
                    PreparedStatement pstmt = (PreparedStatement) (conn.prepareStatement(subjectSql));
                    pstmt.setLong(1, e.getId());
                    pstmt.setLong(2, e.getSubsiteId());
                    pstmt.setString(3, e.getName());
                    pstmt.setString(4, e.getTypeName());
                    pstmt.setString(5, e.getTargetUrl());
                    pstmt.executeUpdate();
                }
                System.out.println("subject表导入完成");
            } else {
                isInsertSuccess = false;
            }
            //插入case_entity表
            if (null != linkedList) {
                System.out.println("正在写case_entity表");
                for (CaseEntity e : linkedList) {
                    PreparedStatement pstmt = (PreparedStatement) (conn.prepareStatement(caseEntitySql));
                    pstmt.setLong(1, e.getId());
                    pstmt.setString(2, e.getName());
                    pstmt.setInt(3, e.getDifficulty());
                    pstmt.setInt(4, e.getDomain());
                    pstmt.setString(5, e.getDescription());
                    pstmt.setLong(6, e.getSubject_id());
                    pstmt.setString(7, e.getCase_id());
                    pstmt.setBoolean(8, e.getIs_public());
                    pstmt.setTimestamp(9, e.getCreate_time());
                    pstmt.setLong(10, e.getApp_id());
                    pstmt.setBoolean(11, e.getIs_deleted());
                    pstmt.setLong(12, Long.parseLong(ownerId));
                    pstmt.setShort(13, e.getPlatform());

                    String string = e.getProperties();
                    Matcher m = Pattern.compile("http://([^\"]*)aliyuncs.com").matcher(string);
                    while (m.find()) {
                        String old = m.group();
                        string = string.replace(old, PROPERTIES_PATH);
                    }
                    pstmt.setString(14, string);
                    pstmt.setLong(15, e.getSubsite_id());
                    pstmt.executeUpdate();
                    //写case_permission表
                    pstmt = (PreparedStatement) (conn.prepareStatement(casePermissionSql));
                    pstmt.setLong(1, 0);
                    pstmt.setString(2, "*");
                    pstmt.setLong(3, e.getId());
                    pstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
                    pstmt.executeUpdate();

                    pstmt = (PreparedStatement) (conn.prepareStatement(casePermissionSql));
                    pstmt.setLong(1, Long.parseLong(ownerId));
                    pstmt.setString(2, "view");
                    pstmt.setLong(3, e.getId());
                    pstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
                    pstmt.executeUpdate();
                }
                System.out.println("case_entity表导入完成");
            } else {
                isInsertSuccess = false;
            }
            //插入case_extends表
            if (null != caseExtendsList) {
                System.out.println("正在写case_extends表");
                for (CaseExtends e : caseExtendsList) {
                    PreparedStatement pstmt = (PreparedStatement) (conn.prepareStatement(caseExtendsSql));
                    pstmt.setShort(1, e.getPlatform());
                    pstmt.setString(2, e.getProperties());
                    pstmt.setLong(3, e.getSubsiteId());
                    pstmt.setLong(4, e.getTargetId());
                    pstmt.setLong(5, e.getId());
                    pstmt.executeUpdate();

                }
                System.out.println("case_extends表导入完成");
            } else {
                isInsertSuccess = false;
            }
            //插入target表
            if (null != targetList) {
                System.out.println("正在写target表");
                for (Target e : targetList) {
                    PreparedStatement pstmt = (PreparedStatement) (conn.prepareStatement(targetSql));
                    pstmt.setLong(1, e.getId());
                    pstmt.setBoolean(2, e.getIsPublic());
                    pstmt.setString(3, e.getName());
                    String string = e.getUrl();
                    Matcher m = Pattern.compile("http://([^\"]*)aliyuncs.com").matcher(string);
                    while (m.find()) {
                        String old = m.group();
                        string = string.replace(old, PROPERTIES_PATH);
                    }

                    pstmt.setString(4, string);
                    pstmt.setLong(5, e.getSubsiteId());
                    pstmt.setInt(6, e.getStatus());
                    pstmt.setTimestamp(7, e.getCreateTime());
                    pstmt.setString(8, e.getMessage());
                    pstmt.setLong(9, Long.parseLong(ownerId));
                    pstmt.executeUpdate();
                    //写target_permission表
                    pstmt = (PreparedStatement) (conn.prepareStatement(targerPermissionSql));
                    pstmt.setLong(1, 0);
                    pstmt.setString(2, "*");
                    pstmt.setLong(3, e.getId());
                    pstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
                    pstmt.executeUpdate();

                    pstmt = (PreparedStatement) (conn.prepareStatement(targerPermissionSql));
                    pstmt.setLong(1, Long.parseLong(ownerId));
                    pstmt.setString(2, "view");
                    pstmt.setLong(3, e.getId());
                    pstmt.setTimestamp(4, new Timestamp(System.currentTimeMillis()));
                    pstmt.executeUpdate();
                }
                System.out.println("target表导入完成");
            } else {
                isInsertSuccess = false;
            }
            //插入target_extends表
            if (null != targetExtendsList) {
                System.out.println("正在写target_extends表");
                for (TargetExtends e : targetExtendsList) {
                    PreparedStatement pstmt = (PreparedStatement) (conn.prepareStatement(targetExtendsSql));
                    pstmt.setLong(1, e.getId());
                    pstmt.setLong(2, e.getTargetId());
                    pstmt.setString(3, e.getPropertyName());
                    pstmt.setString(4, e.getPropertyValue());
                    pstmt.executeUpdate();
                }
                System.out.println("target_extends导入完成");
            } else {
                isInsertSuccess = false;
            }
            //开启外键检测
            stmt.execute(openForeignCheckSql);
            //关闭连接
            stmt.close();
            conn.close();
        } catch (Exception e) {
            isInsertSuccess = false;
            e.printStackTrace();
        }

        return isInsertSuccess;
    }

    /**
     * @param
     * @return java.lang.Object
     * @Description 从文件中读取对象
     * @author 孙加辉
     * @date 2018/10/31 10:19
     */
    private static Object readObjectFromDocument(File file) {
        try {
            ObjectInputStream inputStream = new ObjectInputStream(new FileInputStream(file));
            Object object = inputStream.readObject();
            return object;
        } catch (Exception e) {
            System.out.println("文档读取失败");
            e.printStackTrace();
            return null;
        }
    }

    /**
     * @param
     * @return void
     * @Description 检测mooctest_main_dev数据库是否存在，不存在则新建该数据库
     * @author 孙加辉
     * @date 2018/10/29 14:31
     */
    private static void testDB() {
        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println("数据库已存在");
            conn.close();
        } catch (Exception e) {
            System.out.println("数据库不存在");
            try {
                String newUrl = DB_URL.replace(DBName, "mysql");
                Class.forName(JDBC_DRIVER);
                conn = DriverManager.getConnection(newUrl, USER, PASS);
                stmt = conn.createStatement();
                stmt.executeUpdate("create database " + DBName);
                System.out.println("数据库" + DBName + "创建成功");
                stmt.close();
                conn.close();
            } catch (Exception ee) {
                System.out.println("数据库" + DBName + "创建失败");
            }
        }
    }

    /**
     * @param
     * @return void
     * @Description 检查对应的表存不存在，不存在则新建
     * @author 孙加辉
     * @date 2018/10/29 17:01
     */
    private static void testTable(String[] tableName, String[] createTableSql) {
        //String checkTable = "show tables like \"" + tableName + "\"";
        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            for (int i = 0; i < tableName.length; i++) {
                String checkTable = "show tables like \"" + tableName[i] + "\"";
                ResultSet resultSet = stmt.executeQuery(checkTable);
                if (!resultSet.next()) {
                    System.out.println(tableName[i] + "表不存在");
                    stmt.executeUpdate(createTableSql[i]);
                } else {
                    System.out.println(tableName[i] + "表已存在");
                }
            }
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }


}